package com.qf.dao;

import com.qf.entity.Classes;
import com.qf.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 班级的数据访问层
 * description:
 * author: Ken
 * 公众号：Java架构栈
 */
public class ClassesDao {


    /**
     * 查询全部
     * @return
     */
    public List<Classes> queryAll(){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        List<Classes> classesList = new ArrayList<>();

        String sql = "select * from classes";
        try {
            ps = connection.prepareStatement(sql);
            //没有参数
            resultSet = ps.executeQuery();

            //ORM - 将结果集转换为对象集合
            while(resultSet.next()){
                //每条结果集 都会对应一个班级对象
                Classes classes = new Classes();

                //将班级对象放入List集合
                classesList.add(classes);

                //设置
                classes.setId(resultSet.getInt("id"))
                        .setClassName(resultSet.getString("class_name"))
                        .setClassCount(resultSet.getInt("class_count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }

        return classesList;
    }

    /**
     * 根据id查询班级信息
     * @return
     */
    public Classes queryById(String cid){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        String sql = "select * from classes where id = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setObject(1, cid);
            //执行查询
            resultSet = ps.executeQuery();
            //确定只会返回一个数据
            if (resultSet.next()) {
                Classes classes = new Classes()
                        .setId(resultSet.getInt("id"))
                        .setClassName(resultSet.getString("class_name"))
                        .setClassCount(resultSet.getInt("class_count"));

                return classes;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }
        return null;
    }

    /**
     * 新增班级
     * @return
     */
    public int insert(Classes classes){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;

        String sql = "insert into classes value(?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            ps.setObject(1, classes.getId());
            ps.setObject(2, classes.getClassName());
            ps.setObject(3, classes.getClassCount());

            //执行插入
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
        return -1;
    }

    /**
     * 根据id数组删除班级
     * @return
     */
    public int deleteByIds(String[] ids){
        //id数组不为空
        if (ids == null || ids.length == 0) return -1;

        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;

        //组装sql语句
        StringBuilder sb = new StringBuilder("delete from classes where id in (");
        for (int i = 0; i < ids.length; i++) {
            if (i > 0) sb.append(",");
            sb.append("?");
        }
        sb.append(")");
        System.out.println("批量删除的SQL语句：" + sb.toString());

        //生成预编译的sql
        try {
            ps = connection.prepareStatement(sb.toString());
            //设置参数
            for (int i = 1; i <= ids.length; i++) {
                ps.setObject(i, ids[i-1]);
            }
            //执行SQL
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
        return -1;
    }

    /**
     * 根据id修改班级信息
     * @return
     */
    public int updateById(Classes classes){
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        String sql = "update classes set class_name = ? where id = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setObject(1, classes.getClassName());
            ps.setObject(2, classes.getId());

            //执行修改
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
        return  -1;
    }

    /**
     * 修改班级的人数
     * @param id
     * @param count
     * @param type
     * @return
     */
    public int updateClsCount(Integer id, Integer count, Integer type) throws Exception {
        Connection connection = ConnectionUtils.getConnection();
        System.out.println("修改班级人数获得的连接：" + connection);
        PreparedStatement ps = null;
        String sql = "update classes set class_count = class_count " + (type == 0 ? "+" : "-") + " ? where id = ?";
        System.out.println("最后需要执行的SQL：" + sql);

        try {
            ps = connection.prepareStatement(sql);
            ps.setObject(1, count);
            ps.setObject(2, id);
            return ps.executeUpdate();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
    }
}
